package com.yc.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.eclipse.swt.SWT;
import org.eclipse.swt.graphics.Image;
import org.eclipse.swt.layout.GridData;
import org.eclipse.swt.widgets.ExpandBar;

import com.yc.Idao.GoodsIDao;

public class GoodsDao implements GoodsIDao{

	@Override
	public List<Map<String, Object>> find(Map<String, Object> map) {
		DBHelper db = new DBHelper();
		//动态的SQL语句拼接
		StringBuffer sb = new StringBuffer();
		sb.append("select g_id, g_name, g_price, g_image from f_good where 1 = 1 ");
		//参数集合
		List<Object>params = null;
		//判断map是否为空   意味着无查询条件
		if(null == map || map.isEmpty()) {
			return db.findMutile(sb.toString());
		}
		//意味着有查询参数
		params = new ArrayList<Object>();
		//通过参数  拼接SQL对应条件
		if(map.get("g_name") != null) {
			sb.append(" and g_name like '%" + map.get("g_name") + "%'");
		}
		return db.findMutile(sb.toString(), params.toArray());
	}
	
	public int addGoods(String name, String price, Image image){
		String sql = "";
		return 0;
	}

	@Override
	public int add(String c_cardid, String g_id, String r_id, String f_num, String f_price) {
		DBHelper db = new DBHelper();
		
		String sql;
		
		sql = "select c_money from customer where c_cardid = ?";
		
		if(Double.parseDouble(db.findSingle(sql, c_cardid).get("c_money").toString()) < Double.parseDouble(f_price)) {
			return -1;
		}
		sql = "update  customer set c_money = c_money - ? where c_cardid = ?";
		db.update(sql, f_price, c_cardid);
		
		sql = "insert into f_fw values(f_fw_id.nextval, ?, ?, ?, ?)";
		
		
		
		return db.update(sql, r_id, g_id, f_num, f_price);
	}

	
	/**
	 * 3.2 按条件查询书籍信息
	 * @param map
	 * select f.l_id, c_name, l.r_id, r_type, start_date, end_date, avg(l_price), sum(f_price), avg(l_price) + sum(f_price) totalprice from f_fw f, f_login l, f_room r, customer c
       where f.l_id = l.l_id and c.c_cardid = l.c_cardid and r.r_id = l.r_id group by f.l_id, c_name, l.r_id, r_type, start_date, end_date;
	 * @return
	 */
	@Override
	public List<Map<String, Object>> findGoodsList(Map<String, Object> map) {
		DBHelper db = new DBHelper();
		StringBuffer sb = new StringBuffer();
		sb.append("select l.l_id, c_name, l.r_id, r_type, start_date, end_date, avg(l.l), sum(l.f), avg(l.l) + sum(l.f) totalprice from\r\n" + 
				"(select l.l_id, c_name, l.r_id, r_type, start_date, end_date, avg(l_price) l, sum(nvl(f_price, 0)) f from  \r\n" + 
				"       (select l.l_id, l.r_id, l_price, c_name, r_type, start_date, end_date from f_login l, customer c, f_room r where c.c_cardid = l.c_cardid and r.r_id = l.r_id) l\r\n" + 
				"       full join f_fw f on f.l_id = l.l_id group by l.l_id, c_name, l.r_id, r_type, start_date, end_date) l where 1 = 1" );
		//參數列表
		List<Object> params = null;
		//判断 集合是否为空 否则当传入null值
		if(null == map || map.isEmpty()) {
			sb.append(" group by l.l_id, c_name, l.r_id, r_type, start_date, end_date ");
			return db.findMutile(sb.toString());
		}
		//意味着有查询条件
		params = new ArrayList<Object>();
		//通过参数 拼接对应SQL条件
		if(map.get("l_id") != null) {//根据书籍ISBN编号查询
			sb.append(" and l.l_id = ? ");
			params.add(map.get("l_id"));
		}
		if(map.get("c_name") != null) {//根据书籍名查询
			//模糊查询
			sb.append(" and l.c_name like '%" + map.get("c_name") + "%'");
		}
		if(map.get("r_id") != null) {//根据作者名查询
			//模糊查询
			sb.append(" and l.r_id like '%" + map.get("r_id") + "%'");
		}
		sb.append(" group by l.l_id, c_name, l.r_id, r_type, start_date, end_date");
		return db.findMutile(sb.toString(), params.toArray());
	}
	
	//select g_name, f_num, f_price from f_fw f, f_good g where f.g_id = g.g_id and l_id = '1001';
	public List<Map<String, Object>> findGoods(String l_id){
		String sql = "select g_name, f_num, g_price, f_price, g_image from f_fw f, f_good g where f.g_id = g.g_id and l_id = ?";
		DBHelper db = new DBHelper();
		return db.findMutile(sql, l_id);
	}

	@Override
	public Map<String, Object> find(String r_id) {
		String sql = "select l_id from f_login where r_id = ? and end_date is null";
		DBHelper db = new DBHelper();
		return db.findSingle(sql, r_id);
	}
	
	@Override
	public int addappraise(String r_id, String id, String datil, byte[] bimage) {
		String sql = "insert into f_appraise values((select l_id from f_login where end_date is null and start_date = (select start_date from f_order where r_id = ?)), ?, " + 
				"?, null, ?)";
		DBHelper db = new DBHelper();
		return db.update(sql, r_id, id , datil, bimage);
	}
	
	
	@Override
	public List<Map<String, Object>> findAddappraise() {
		String sql = "select r_id, c_name, a_id, a_datil, a_reply, a_image from f_appraise a, f_login l, customer c\r\n" + 
				" where a.l_id = l.l_id and c.c_cardid = l.c_cardid";
		DBHelper db = new DBHelper();
		return db.findMutile(sql);
	}

	public int addGoods(String name, String price, byte[] image){
		String sql = "insert into f_good values(f_good_id.nextval, ?, ?, ?)";
		DBHelper db = new DBHelper();
		return db.update(sql, name, price, image);
	}

	public int updateGoods(String name, String price) {
		String sql = "update f_good set g_price = ? where g_name = ?";
		DBHelper db = new DBHelper();
		return db.update(sql, price, name);
	}
	
	@Override
	public List<Map<String, Object>> findcustomer(Map<String, Object> map) {
		DBHelper db = new DBHelper();
		StringBuffer sb = new StringBuffer();
		sb.append("select c_id, c_name, c_pwd, c_cardid, c_tel, c_sex, c_money, c_eml  from  customer \r\n" + 
				"where 1 = 1  ");
		//參數列表
		List<Object> params = null;
		//判断 集合是否为空 否则当传入null值
		if(null == map || map.isEmpty()) {
			return db.findMutile(sb.toString());
		}
		//意味着有查询条件
		params = new ArrayList<Object>();
		//通过参数 拼接对应SQL条件
		if(map.get("c_name") != null) {//根据书籍名查询
			//模糊查询
			sb.append(" and c_name like '%" + map.get("c_name") + "%'");
		}
		if(map.get("c_cardid") != null) {//根据作者名查询
			//模糊查询
			sb.append(" and c_cardid like '%" + map.get("c_cardid") + "%'");
		}
		if(map.get("c_tel") != null) {//根据作者名查询
			//模糊查询
			sb.append(" and c_tel like '%" + map.get("c_tel") + "%'");
		}
		if(map.get("c_eml") != null) {//根据作者名查询
			//模糊查询
			sb.append(" and c_eml like '%" + map.get("c_eml") + "%'");
		}		
		return db.findMutile(sb.toString(), params.toArray());
	} 
	
	public int addAdmin(String name, String pwd, byte[] image) {
		String sql = "insert into f_admin values(f_admin_id.nextval, '2', ?, ?, ?)";
		DBHelper db = new DBHelper();
		return db.update(sql, name, pwd, image);
	}
	
	public List<Map<String, Object>> findAdmin() {
		String sql = "select a_id, a_name, a_pwd, a_image from f_admin";
		DBHelper db = new DBHelper();
		return db.findMutile(sql);
	}
	
	public int reply(String reply, String r_id, String c_name) {
		String sql = "update (select r_id, c_name, a_id, a_datil, a_reply from f_appraise a, f_login l, customer c where a.l_id = l.l_id and c.c_cardid = l.c_cardid) "
				+ " set a_reply = ? where r_id = ? and c_name = ?";
		DBHelper db = new DBHelper();
		return db.update(sql, reply, r_id, c_name);
	}
	
	public Map<String,Object> findprice(String l_id){
		String sql = "select r_price, TO_NUMBER(end_date- start_date)\r\n" + 
				"day from f_room r, f_login l where  l_id = ? and l.r_id = r.r_id and end_date is not null";
		DBHelper db = new DBHelper();
		return db.findSingle(sql, l_id);
	}
}
